/*
 * HierarchyTable.java - SQL operations with the table 'hierarchy'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'hierarchy' in the database 'context'.
 *  See Ontology.WDHier
 */
public class HierarchyTable extends RelationTable {
    
    /** Parent class */
    public ClassTable parent_class;
    
    /** Child class */
    public ClassTable child_class;
    
    /** id of ontology is stored in parent_class.onto_id and child_class.onto_id */
    //public int      onto_id;
    
    
    /** Creates a new instance of HierarchyTable */
    public HierarchyTable() {
    }

    
    /** Inserts element to the table 'hierarchy' in MySQL database.
     * 
     * SQL example:
     * INSERT INTO hierarchy (parent_class_id,child_class_id,onto_id) VALUES(42,41,101);<br>
     *
     *  @param parent_class_id  id of parent class
     *  @param child_class_id   id of child class
     */
    public static void insert ( java.sql.Connection conn,
                                int parent_class_id, int child_class_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable parent = ClassTable.getByClassID(conn, parent_class_id);
        ClassTable child  = ClassTable.getByClassID(conn,  child_class_id);
        if(null == parent || null == child)
            return;
        
        HierarchyTable h = get(conn, parent_class_id, child_class_id);
        if (h != null) 
            return;
        
        try 
        {
            s = conn.createStatement ();
            str_sql = "INSERT INTO hierarchy (parent_class_id,child_class_id,onto_id) "+ 
                            "VALUES ("  + parent_class_id + 
                                    "," + child_class_id  +
                                    "," + parent.onto_id  + ")";
            s.executeUpdate (str_sql);
            
        }catch(SQLException ex) {
            System.err.println("SQLException (context.HierarchyTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    
    /** Selects data from table 'class'
     * if there is pair of ontology classes parent-child in the table 'hierarchy'.
     *
     *  SQL:
     *  SELECT * FROM hierarchy WHERE parent_class_id=42 AND child_class_id=41 AND onto_id=101;
     *  
     *  @return HierarchyTable object with initialized fields: parent_class, child_class.
     */
    public static HierarchyTable get (java.sql.Connection conn,int parent_class_id,int child_class_id) {
                
        HierarchyTable h = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable parent = ClassTable.getByClassID(conn, parent_class_id);
        ClassTable child  = ClassTable.getByClassID(conn,  child_class_id);
        if(null == parent || null == child)
            return null;
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT * FROM hierarchy " +
                        "WHERE parent_class_id=" + parent_class_id + 
                        " AND child_class_id="   +  child_class_id + 
                        " AND onto_id="          + parent. onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                h = new HierarchyTable ();
                h.parent_class = parent;
                h.child_class  = child;
                h.id           = rs.getInt("id");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.HierarchyTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }    
        return h;
    }
    
    
    /** Selects data from table 'hierarchy'
     * if there is pair of ontology classes parent-child in the table 'hierarchy'.
     *
     *  SQL:
     *  SELECT * FROM hierarchy WHERE id=3;
     *  
     *  @return HierarchyTable object with initialized fields: parent_class, child_class.
     */
    public static HierarchyTable getByID (java.sql.Connection conn,int id) {
        
        HierarchyTable h = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT * FROM hierarchy WHERE id=" + id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                int parent_class_id = rs.getInt("parent_class_id");
                int child_class_id  = rs.getInt("child_class_id");
                
                ClassTable parent = ClassTable.getByClassID(conn, parent_class_id);
                ClassTable child  = ClassTable.getByClassID(conn, child_class_id);
                if(null == parent || null == child) {
                    System.out.println("Error in context.HierarchyTable.java getByID()):: "+
                    "class parent and/or child are null, parent_class_id="+parent_class_id+
                    ", child_class_id="+child_class_id+", id="+id);
                } else {
                    h = new HierarchyTable ();
                    h.id            = rs.getInt("id");
                    h.parent_class  = parent;
                    h.child_class   = child;
                }
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.HierarchyTable.java getByID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s  != null) {   try { s.close();  } catch (SQLException sqlEx) { }  s  = null; }
        }    
        return h;
    }
    
    
    /** Deletes hierarchy's row by class id and ontolody id.
     *  SQL:        
     *  DELETE FROM hierarchy WHERE (parent_class_id=41 OR child_class_id=41) AND onto_id=101;
     */
    public static void delete (java.sql.Connection conn,int class_id,int onto_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM hierarchy " +
                        "WHERE (parent_class_id=" + class_id + 
                            " OR child_class_id=" + class_id + 
                              ") AND onto_id="    + onto_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.HierarchyTable.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }

}
